package db.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import control.ConnectionManage;
import db.parking.beans.SaveDisableParking;

/**
 * AdminSaveDisableParking  manages all requests From,To table DisabledParking
 * @author Operations
 *
 */
public class AdminSaveDisableParking {

	private static Connection conn = ConnectionManage.getInstance()
			.getConnection();
	
	
	
	
	/**
	 * @param SaveDisableParking bean
	 * @return true if insert in done well
	 * @throws Exception
	 */
	public static boolean insert(SaveDisableParking bean) throws Exception {//insert new canceled order
		String sql = "INSERT into  savedisableparking  (lotNo ,empID ,startDay  ,x ,y ,z ,status) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?);";		
		
		ResultSet keys = null;
		
		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {
			stmt.setInt(1, bean.getLotNo());
			stmt.setInt(2, bean.getEmployeeId());
			stmt.setTimestamp(3, bean.getFrom());
			stmt.setInt(4, bean.getX());
			stmt.setInt(5, bean.getY());
			stmt.setInt(6, bean.getZ());
			stmt.setInt(7, bean.getStatus());
			int affected = stmt.executeUpdate();
			if (affected == 1) {
				return true;
			} else {
				System.err.println("No rows affected");
				return false;
			}

		} catch (SQLException e ) {
			System.err.println(e + "  AdminSaveDisableParking->insert");
			return false;
		} finally {
			if (keys != null)
				keys.close();
		}
	}
	
	
	/**
	 * Functions updates Status of saved parking
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static boolean update(SaveDisableParking bean) throws Exception {
		String sql = "UPDATE savedisableparking SET endDay = ?, status=?"+
				"WHERE lotNo = ? AND empID=? AND x=? AND y=? AND z=? "  
				+ "VALUES (?, ?, ?, ?, ?, ?, ?);";
		

		
		ResultSet keys = null;
		
		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {
			stmt.setTimestamp(1, bean.getTo());
			stmt.setInt(2, bean.getStatus());
			stmt.setInt(3, bean.getLotNo());
			stmt.setInt(4, bean.getEmployeeId());
			stmt.setInt(5, bean.getX());
			stmt.setInt(6, bean.getY());
			stmt.setInt(7, bean.getZ());
			int affected = stmt.executeUpdate();
			if (affected == 1) {
				return true;
			} else {
				System.err.println("No rows affected");
				return false;
			}

		} catch (SQLException e ) {
			System.err.println(e + "  AdminSaveDisableParking->insert");
			return false;
		} finally {
			if (keys != null)
				keys.close();
		}
	}

	
	
	/**
	 * Functions returns all reports of the saved parking
	 * @param firstDay
	 * @param lastDay
	 * @param lotNo
	 * @return
	 * @throws Exception
	 */
	public static SaveDisableParking[] getAllReport(Timestamp firstDay,Timestamp lastDay, int lotNo) throws Exception {
		String sql = "SELECT * FROM savedisableparking where lotNo=? And status=? AND startDay>=? AND  startDay<?";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setInt(1, lotNo);
			stmt.setInt(2, 2); 	//onley disabeld parking
			stmt.setTimestamp(3, firstDay);
			stmt.setTimestamp(4, lastDay);
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			SaveDisableParking bean[] = new SaveDisableParking[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new SaveDisableParking();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setLotNo(rs.getInt("lotNo"));
				bean[rowCount].setEmployeeId(rs.getInt("empID"));
				bean[rowCount].setFrom(rs.getTimestamp("startDay"));
				bean[rowCount].setTo(rs.getTimestamp("endDay"));
				bean[rowCount].setX(rs.getInt("x"));
				bean[rowCount].setY(rs.getInt("y"));
				bean[rowCount].setZ(rs.getInt("z"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			}
			return bean;
		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	/**
	 * Functions returns number of disabled parking spots in specified lot
	 * @param firstDay
	 * @param lastDay
	 * @param lotNo
	 * @return
	 * @throws Exception
	 */
	public static int numOfDisabledParking(Timestamp firstDay,Timestamp lastDay, int lotNo) throws Exception {
		String sql = "SELECT * FROM savedisableparking where lotNo=? And status=? AND startDay>=? AND  startDay<?";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setInt(1, lotNo);
			stmt.setInt(2, 2); 	//onley disabeld parking
			stmt.setTimestamp(3, firstDay);
			stmt.setTimestamp(4, lastDay);
			rs = stmt.executeQuery();
			rs.last();
			int count=rs.getRow();
			return count;
		} catch (SQLException e) {
			System.err.println(e);
			return -1;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}
}
		